begin;
/*ALTER TABLE gsp_commercial_details ADD column ship_status character varying;*/
drop view gsp_commercial_details_view;
create or replace view gsp_commercial_details_view as SELECT 
gsp_id,
comcb.company_name as "buyer_name",
comca.company_name as"account_name",
ms.shipment_mode_name,
port_name,
to_char(ship_date,'dd/mm/yyyy') as "ship_date",
remarks, 
support_documents,
comca.address as "exp_address",
comca.city_name as "city_id_from",
pcm.shipment_mode_name as "pre_carriage_mode",
pcf.city_name as "pre_carriage_from",
pct.city_name as "pre_carriage_to",
comca.country_name as countryfrom,
mca1.city_name as "city_from",
msa.state_name as "state_from",
mcoa.country_name as "country_from",
comca.zipcode as "zipcode_from",
comcb.address as "address_to",
comcb.city_name as "city_id_to",
comcb.country_name as "countryto",
mcb1.city_name as "city_to",
msb.state_name as "state_to",
mcob.country_name as "country_to",
comcb.zipcode as "zipcode_to",
book_nos,
origin_requirements,
ship_status,
signatory
from gsp_commercial_details gcd 
left join _mcompany mcb on mcb.company_id=gcd.buyer_id 
left join _mcompany mca on mca.company_id=gcd.account_id 
left join company_contact cc on cc.company_id=mca.company_id 
left join company_contact ccb on ccb.company_id=mcb.company_id 
left join _mship_mode ms on ms.shipment_mode_id=gcd.ship_mode_id 
left join _mport mp on mp.port_id=gcd.ship_to_port 
left join company_contact comca on comca.company_id=mca.company_id 
left join company_contact comcb on comcb.company_id=mcb.company_id 
left join _mcity mca1 on mca1.city_id=gcd.ship_from_city 
left join _mcountry mcoa on mcoa.country_id=gcd.ship_from_country 
left join _mstate msa on msa.state_id=comca.state 
left join _mcity mcb1 on mcb1.city_id=comcb.city_name 
left join _mcountry mcob on mcob.country_id=gcd.ship_to_country 
left join _mship_mode pcm on pcm.shipment_mode_id=gcd.pre_carriage_mode 
left join _mcity pcf on pcf.city_id=gcd.pre_carriage_from
left join _mcity pct on pct.city_id= gcd.pre_carriage_to
left join _mstate msb on msb.state_id=comcb.state where status='act';

commit;